package com.example.bysj.Dao;
//201902104061徐中堂
import com.example.bysj.domain.Student;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
@Slf4j
//声明数据库访问类
@Repository("studentDao")
public class StudentDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private TeacherDao teacherDao;
    @Autowired
    private StudentClassDao studentClassDao;


    /**
     * 添加
     * @param student 添加的学生
     * @return
     */
    public boolean addStudent(Student student){
        String sql = "INSERT INTO student (id,name,no,remarks,teacher_id,studentClass_id) VALUES(?,?,?,?,?,?)";
        int rowAffected = jdbcTemplate.update(sql,
                student.getId(),
                student.getName(),
                student.getNo(),
                student.getRemarks(),
                student.getSupervisor().getId(),
                student.getStudentClass().getId());
        if (rowAffected != 1){
            return false;
        } else {
            return true;
        }
    }

    /**
     * 批量增加
     * @param addStudents
     * @return
     */
    public boolean addStudents(List<Student> addStudents){
        String sql = "INSERT INTO student (name,no,remarks,teacher_id,studentClass_id) VALUES(?,?,?,?,?)";
        for (int i = 0; i<addStudents.size(); i++){
            int rowAffected = jdbcTemplate.update(sql,
                    addStudents.get(i).getName(),
                    addStudents.get(i).getNo(),
                    addStudents.get(i).getRemarks(),
//                    addStudents.get(i).getDepartment().getId(),
                    addStudents.get(i).getSupervisor().getId(),
                    addStudents.get(i).getStudentClass().getId());
            if (rowAffected != 1){
                return false;
            }
        }
        return true;
    }

    /**
     * 查询所有
     * @return
     */
    public List<Student> getAll(){
        String sql = "SELECT * from Student";
        List<Student> students = jdbcTemplate.query(sql, new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setRemarks(rs.getString("remarks"));
//                student.setDepartment(departmentDao.getById(rs.getInt("department_id")));
                student.setStudentClass(studentClassDao.getById(rs.getInt("studentClass_id")));
                student.setSupervisor(teacherDao.find(rs.getInt("teacher_id")));
                log.debug(student.toString());
                System.out.println(student.toString());
                return student;
            }
        });
        return students;
    }

    /**
     * 根据id查找
     * @param id
     * @return
     */
    public Student getOneById(int id){
        String sql = "SELECT * FROM Student where id=?";
        List<Student> students = jdbcTemplate.query(sql, new Object[]{id}, new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setRemarks(rs.getString("remarks"));
//                student.setDepartment(departmentDao.getById(rs.getInt("department_id")));
                student.setStudentClass(studentClassDao.getById(rs.getInt("studentClass_id")));
                student.setSupervisor(teacherDao.find(rs.getInt("teacher_id")));
                return student;
            }
        });
        if (!students.isEmpty()){
            return students.get(0);
        } else
            return null;
    }

    /**
     * 更新
     * @param student
     * @return
     */
    public boolean updateStudent(int id,Student student){
        String sql = "UPDATE Student SET name=?,no=?,remarks=?,teacher_id=?,studentClass_id=?WHERE id=?";
        int rowAffected = jdbcTemplate.update(sql,
                student.getName(),
                student.getNo(),
                student.getRemarks(),
                student.getSupervisor().getId(),
                student.getStudentClass().getId(),
//                student.getDepartment().getId(),
                id);
        if (rowAffected!=1){
            return false;
        } else {
            return true;
        }
    }

    /**
     * 删除
     * @param id
     * @return
     */
    public boolean deleteStudent(int id){
        String sql = "delete from student where id =?";
        int rowAffected = jdbcTemplate.update(sql,id);
        if (rowAffected!=1){
            return false;
        } else
            return true;
    }
}
